﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace OpenMobileStoreModel
{
    /// <summary>
    /// Store contains common data for the model and user preferences.
    /// Database table "store" is used for saving user preferences.
    /// Store implements Singleton pattern. It is accessed by Store.Instance.
    /// </summary>
    public class Store
    {
        private SQLiteConnection mDb;
        private static Store mInstance = null;
        static private string mCode;
        static private long mRevision;
        static private string mSyncIpAddress;

        /// <summary>
        /// Code is unique identifier for the device.
        /// It allows multiple devices to export their closed documents. 
        /// </summary>
        public string Code
        {
            get { return mCode; }
            set
            {
                Db.Open();
                using (Db)
                {
                    SQLiteCommand cmd = new SQLiteCommand("UPDATE store SET name=@name;", Db);
                    SQLiteParameter prm = new SQLiteParameter("@name", DbType.String);
                    prm.Value = value;
                    cmd.Parameters.Add(prm);
                    cmd.ExecuteNonQuery();
                    mCode = value;
                }
            }
        }

        /// <summary>
        /// IP address of the synchronization service.
        /// </summary>
        public string SyncIpAddress
        {
            get { return mSyncIpAddress; }
            set
            {
                Db.Open();
                using (Db)
                {
                    SQLiteCommand cmd = new SQLiteCommand("UPDATE store SET sync_server=@name;", Db);
                    SQLiteParameter prm = new SQLiteParameter("@name", DbType.String);
                    prm.Value = value;
                    cmd.Parameters.Add(prm);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Sqlite database connection that is used by the model.
        /// </summary>
        public SQLiteConnection Db
        {
            get
            {
                return mDb;
            }
        }  
      
        /// <summary>
        /// Store implements Singleton pattern. It is accessed by Store.Instance.
        /// </summary>
        public static Store Instance
        {
            get
            {
                if (mInstance == null)
                {
                    SQLiteConnection db = new SQLiteConnection(Config.DB_CONNECTION_STRING);
                    mInstance = new Store(db);
                }
                return mInstance;
            }
        }

        /// <summary>
        /// Create database if not exists and initialize Store with data from database.
        /// </summary>
        /// <param name="aDb"></param>
        private Store(SQLiteConnection aDb)
        {
            mDb = aDb;
            Db.Open();
            using (Db)
            {
                // Create database if not exists
                SQLiteCommand cmd = new SQLiteCommand(
                    "CREATE TABLE IF NOT EXISTS store (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                      "code TEXT," +
                  "revision INTEGER," +
           "sync_ip_address TEXT);", Db);
                cmd.ExecuteNonQuery();

                cmd.CommandText =
                    "CREATE TABLE IF NOT EXISTS product (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                  "revision INTEGER," +
                      "type INTEGER," +
                      "code TEXT," +
                      "name TEXT," +
                  "quantity REAL," +
                     "price REAL);";
                cmd.ExecuteNonQuery();

                cmd.CommandText =
                    "CREATE TABLE IF NOT EXISTS invoice (" +
                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                      "type INTEGER," +
                        "nr INTEGER," +
             "contragent_id INTEGER," +
                      "date TEXT," +
                     "total REAL," +
                      "paid REAL);";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT OR IGNORE INTO store VALUES (1, 'POS 001', 0, 'http://192.168.55.100:65000');";
                cmd.ExecuteNonQuery();

                // Read store data from database
                cmd.CommandText = "SELECT * FROM STORE LIMIT 1;";
                SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                using (rdr)
                {
                    rdr.Read();
                    mCode = rdr.GetString(1);
                    mRevision = rdr.GetInt64(2);
                    mSyncIpAddress = rdr.GetString(3);
                }
            }
        }
    }
}

